/*
** 
** Author: Tsihang, Beijing, China
** Time: 23:00, September 22, 2011      
** 
** This C file is prepared for Sqlite3 database testing. Some API(S) are surpplied 
** by the author. More information wanted, you can email to isoledadcache@gmail.com  
**
** int sqlite3_open(const char * basename, sqlite3 *database) to open a database that named "basename",
       and pointed by sqlite3 *;
**
** int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**) to execute the sql;
**
** const char *sqlite3_errmsg(sqlite3*) to deal the error messge;
**   
** int sqlite3_close(sqlite3 *database) to close a database.
**
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <errno.h>

#include "../inc/sqlite.h"


/* We can use this defination to debug our own function */
#define _TABLE_
#define _SELECT_
//#define _INSERT_
//#define _DELETE_
#define _DEBUGS_
#define _CALLBACK_
#define SIZE 256 
char sql_cmd[256];
extern  char * sql_result[255];
char result_one[1024];

sqlite3 *_db;
char *databuff = NULL;
#ifdef _TABLE_
#define OPENSQLITE3(db) \
	_openSqlite3(db)
#define CLOSESQLITE3(db) \
	_closeSqlite3(db)

#define PRINTK \
	printf("Database Closed\n");

/* open a database, if there is not one, creates it then */
int _openSqlite3(char *database) { return sqlite3_open(database, &_db); }
/* close a database */
int _closeSqlite3(char *database) { sqlite3_close(_db); PRINTK; return 0;}
#endif 

int sqlite_callback(char * sql, sqlite3 *_db)
{
	char**result = 0;
	int nrow = 0;
	int ncolumn = 0;
	char *zeMsg = NULL;
	int i, j;

	sqlite3_get_table(_db, sql , &result , &nrow , &ncolumn, &zeMsg);
	printf("%d, %d\n", nrow, ncolumn);
	for(i = 0; i < (nrow + 1); i++){
		//printf("%d.", i) ; 
		printf( "%d. %s", i, result[i]);
	}
	/* free the space that hold by "result" */
	sqlite3_free_table(result);
    return 0;
}
// Not good
int query_callback(void* p, int argc, char** argv, char** argvv)
{
    	(*(int*)p)++;
	int i;
	int len = 0;
	char t[256] = {0};	
	void * real_ret;
	
	printf("sizeof(databuff)= %d\n", sizeof(databuff));
	for(i = 0; i < argc; i++) {

		//printf("%s = %s  ", argvv[i], argv[i] ? argv[i]: "NULL");
		memset(&t[0], '\0', 256);
		sprintf(t, "%s = %s  ", (char *)argvv[i], argv[i] ? (char *)argv[i]: "NULL");
		
		real_ret = realloc(databuff, (sizeof(databuff) + strlen(t) + 1));
		if(real_ret == NULL) {
			printf("Realloc error\n");
			perror("realloc:");
			return;
		}
		printf("sizeof(databuff) = %d\n", strlen(databuff));
		//strcat(databuff, t);
		memcpy(databuff +  (sizeof(databuff) + strlen(t) + 1), t, strlen(t));
		printf("databuff : %s\n", databuff);
		len+=strlen(t);
	}
	*(databuff + len) = '\n';
    	return 0;
}
int number = 0;

int _selectSqlite3(char *database, char * sql)
{
	char *eMsg = NULL;
	//extern sqlite3 *_db;
    int iret;

	if(NULL==sql)
		return 1;
	
/* Open db */
	if(OPENSQLITE3(database)){
		printf("Failed to open database %s\n", database);
		CLOSESQLITE3(database);
		return 1;
	}
#ifdef _DEBUGS_
#undef _DEBUGS_
#endif
	sprintf(sql_cmd, "%s", sql);
//printf("----- %s\n", sql);
/* Execute SQL for select database */
	//iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);

#ifdef _DEBUGS_
    	printf("number = %d\n", number);
    	if(iret == SQLITE_OK && eMsg == NULL)
       	printf("%s: Yes!\n", sql_cmd);
    	else
        	printf("%s: %s!\n", sql_cmd, eMsg);
#endif

    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3(database);
    	memset(sql_cmd, 0, sizeof(sql_cmd));
	return 0;
}
 #if 0
int _insertSqlite3(const char * name,  const char * sex, const char * age, const char * addr)
{
    char *eMsg = NULL;
    extern sqlite3 *_db;
    int iret = 10;
    int number;
    /* Open db */
	if(OPENSQLITE3)
	{
		printf("Failed to open database %s\n", DB);
		CLOSESQLITE3;
		return 1;
	}
    printf("Inserting...\n");
    sprintf(sql_cmd, "insert into member values(\'%s\', \'%s\', \'%s\', \'%s\');", name, sex, age, addr);   
    /* Execute SQL for select database */
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);

#ifdef _DEBUGS_
	sprintf(sql_cmd, "%s %s %s %s", name, sex, age, addr);
	if(iret == SQLITE_OK && eMsg == NULL)
	  printf("%s: Yes\n", sql_cmd);
	else
	   printf("%s: %s\n", sql_cmd, eMsg);
#endif
    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3;
    
	return 0;
}

int _deleteSqlite3(const char* values)
{
	char *eMsg = NULL;
	extern sqlite3 *_db;
    int iret = 10;
    int number;
    /* Open db */
	if(OPENSQLITE3)
	{
		printf("Failed to open database %s\n", DB);
		CLOSESQLITE3;
		return 1;
	}
    printf("Deleting...\n");
    
	sprintf(sql_cmd, "delete from member where name=\'%s\';", values);   
    /* Execute SQL for select database */
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);
#ifdef _DEBUGS_    
    if(iret == SQLITE_OK && eMsg == NULL)
        printf("%s: Yes!\n", sql_cmd);
    else
        printf("%s: %s!\n", sql_cmd, eMsg);
#endif
    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3;
    
	return 0;
}

/* Main function */
int main(int argc, char * argv[])
{
	printf("Exe database named \"../database/user.db\"\n");
	printf("Wating ...\n");
	printf("\n");
#ifdef _SELECT_
	printf("Select result is: \n");
	printf("---------------------------------------\n");
	    _selectSqlite3(DB, "select * from member");
	printf("---------------------------------------\n");
#endif
	
#ifdef _INSERT_
	printf("\n");
	printf("Insert result is: \n");
	printf("---------------------------------------\n");
	    _insertSqlite3(argv[1], argv[2], argv[3], argv[4]);
	printf("---------------------------------------\n");
#endif

#ifdef _DELETE_
	printf("\n");
	printf("Delete result is: \n");
	printf("---------------------------------------\n");
    	    _deleteSqlite3(argv[1]);
	printf("---------------------------------------\n");
#endif
	return 0;
}
#endif
